package Control;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import jdbchelper.ConnectionPool;
import jdbchelper.JdbcHelper;
import jdbchelper.PooledDataSource;

import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;

public class MySQLController {
	private static JdbcHelper source = null;
	private static Connection con;
	//private static String url = "144.76.19.105";
	private static String url = "127.0.0.1";
	
	public MySQLController (){
		if(source == null){
			MysqlConnectionPoolDataSource ds = new MysqlConnectionPoolDataSource();
			ds.setServerName(url);
			ds.setPort(3306);
			ds.setDatabaseName("ooadpj");
			//ds.setUser("ooadpj");
			//ds.setPassword("123456");
			ds.setUser("root");
			ds.setPassword("zimin95");
			
			ConnectionPool pool = new ConnectionPool(ds, 10);
			PooledDataSource pooledSource = new PooledDataSource(pool);
			source = new JdbcHelper(pooledSource);
		}
	}

	/*************************************************************
	*  Method Name: testDriver
	*  Input Parameter: nil
	*  Purpose: To test if the driver is properly installed
	*  (whether your J-Connector for MySQL is installed properly)
	*  Return: nil
	*************************************************************/
	public void testDriver() throws Exception{
		System.out.println("Initializing Server... ");

		try{
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("Driver Found");
		}
		catch(ClassNotFoundException e){
			System.out.println("Driver Not Found, exiting..");
			throw(e);
			}
		}
	
	public void getConnection(){
		try {
			con = source.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/*************************************************************
	*  Method Name: readRequest
	*  Input Parameter: String (database query)
	*  Purpose: Obtain the result set from the db query
	*  Return: resultSet (records from the query)
	*************************************************************/
	public ResultSet readRequest(String dbQuery){
		ResultSet rs = null;
		System.out.println("DB Query: " + dbQuery);
		try{
			//Create a statement object
			Statement stmt = con.createStatement();
			stmt.setFetchSize(5);
			//Execute an SQL query and get the result
			rs = stmt.executeQuery(dbQuery);
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return rs;
	}

	/*************************************************************
	*  Method Name: updateRequest
	*  Input Parameter: String (database query)
	*  Purpose: Execute update using the db query
	*  Return: int (count is 1 if successful)
	*************************************************************/
	public int updateRequest(String dbQuery){
		int count = 0;
		System.out.println("DB Query: " + dbQuery);
		try{
			//Create a statment object
			Statement stmt = con.createStatement();
			stmt.setFetchSize(5);
			//Execute an SQL query and get the result
			count = stmt.executeUpdate(dbQuery);
			stmt.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return count;
	}

	/*************************************************************
	*  Method Name: getColumnNames
	*  Purpose: Obtain the column names of the result set
	*  Return: int (count is 1 if successful)
	*************************************************************/
	public String[] getColumnNames(ResultSet rs){
		String[] columnNames = null;
		try{
			//Get result set meta data
			ResultSetMetaData rsmd = rs.getMetaData();
			columnNames = new String[rsmd.getColumnCount()];
			
			//Get the column names; column indices start from 1
			for(int i = 0; i < columnNames.length; i++){
				columnNames[i] = rsmd.getColumnName(i+1);
			}
		}
		catch(SQLException e){
			System.out.println("Error in getColumnNames() in MySQLController");
			e.printStackTrace();
		}
		return columnNames;
	}
	
	/*************************************************************
	*  Method Name: terminate
	*  Input Parameter: nil
	*  Purpose: Close db connection
	*  Return: nil
	*************************************************************/
	public void terminate(){
		//close connection
		try{
			source.freeConnection(con);
			System.out.println("Connection is closed");
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public static void main(String[] arg)throws Exception{
		MySQLController db = new MySQLController();
		db.testDriver();
		db.getConnection();
	}
}
